﻿using Dapper;
using System.Collections.Generic;
using System.Data.SqlClient;
using yunbo.Model.BIW;
using yunbo.Model.SM;

namespace yunbo.Repositorys.BIWRepositorys
{
    public class WMS_KCRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        public List<WMS_KC> GetWMS_KC(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.kc_code,a.kc_id,d.ck_name,e.wl_code,c.kq_name,b.kw_name,e.wl_name,e.wl_type,wl_size,a.kc_sl,a.kc_jhsl from [dbo].[wms_kc] a\r\ninner join [dbo].[wbd_kw] b on b.[kw_code]=a.[kw_code]\r\ninner join [dbo].[wbd_kq] c on c.[kq_code]=a.[kq_code]\r\ninner join [dbo].[wbd_ck] d on d.[ck_code]=a.[ck_code]\r\ninner join [dbo].[bd_wl] e on e.[wl_code]=a.[wl_code]\r\nwhere a.ck_code='{code}'";
                List<WMS_KC> wms_kcs = connection.Query<WMS_KC>(sql).ToList();
                return wms_kcs;
            }
        }
        public int UpdateWMS_KC(int id,int sl)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_kc] set [kc_jhsl]=[kc_jhsl]+{sl} where [kc_id]={id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int UpdateWMS_KCSL(string code, int sl)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_kc] set [kc_sl]=[kc_sl]-{sl},[kc_jhsl]=[kc_jhsl]-{sl},[kc_krkl]=[kc_krkl]+{sl} where [kc_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int PostWMS_KC(TJWMS_KC tJWMS_KC)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into wms_kc(kc_code,kw_code,kq_code,ck_code,wl_code,kc_sl,kc_jhsl,kc_bz,kc_zdsl,kc_krkl)\r\n" +
                    $"values('{tJWMS_KC.kc_code}','{tJWMS_KC.kw_code}','{tJWMS_KC.kq_code}','{tJWMS_KC.ck_code}','{tJWMS_KC.wl_code}',{tJWMS_KC.kc_sl},0,'111',10000,10000-{tJWMS_KC.kc_sl})";
                int rs = connection.Query<int>(sql).FirstOrDefault();
                return rs;
            }
        }
        public int UpdateWMS_KCCXSL(string code, int sl)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_kc] set [kc_sl]=[kc_sl]+{sl},[kc_jhsl]=[kc_jhsl]+{sl},[kc_krkl]=[kc_krkl]-{sl} where [kc_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public string GetWMS_KCDHDL(string ck_code, string kq_code, string kw_code, string wl_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select kc_code from [dbo].[wms_kc] where kw_code='{kw_code}'and kq_code='{kq_code}'and ck_code='{ck_code}' and wl_code='{wl_code}'";
                string rs= connection.Query<string>(sql).FirstOrDefault();
                return rs;
            }
        }
        public int UpdateWMS_KCXGSL(string code, int sl)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_kc] set [kc_sl]=[kc_sl]+{sl},[kc_krkl]=[kc_krkl]-{sl} where [kc_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
       public List<LLCKWMS_KC> GetLLCKWMS_KC(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.kc_id,a.ck_code,a.kc_sl,b.ck_name,a.[kc_jhsl] from [dbo].[wms_kc] a\r\ninner join [dbo].[wbd_ck] b on b.[ck_code]=a.[ck_code]\r\nwhere a.wl_code='{code}'";
                List< LLCKWMS_KC> wms_kcs = connection.Query<LLCKWMS_KC>(sql).ToList();
                return wms_kcs;
            }
        }
        public FYCXWMS_KC GetFYCXWMS_KC(int PageNumber, int PageSize, string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.kq_code,a.kw_code,a.kc_id,b.kq_name,c.kw_name,d.wl_code,d.wl_name,e.dictionary_name,d.wl_size,a.kc_sl,f.dw_name,d.wl_dj from  [dbo].[wms_kc] a\r\n" +
                    $"inner join [dbo].[wbd_kq] b on a.kq_code=b.kq_code\r\n" +
                    $"inner join [dbo].[wbd_kw] c on a.kw_code=c.kw_code\r\n" +
                    $"inner join [dbo].[bd_wl] d on a.wl_code=d.wl_code\r\n" +
                    $"inner join [dbo].[dictionary] e on e.dictionary_id=d.wl_fl\r\n" +
                    $"inner join [dbo].[bd_dw] f on f.dw_id=d.wl_dw\r\n" +
                    $"where ck_code='{code}'\r\n";
                List<CXWMS_KC> cXWMS_KCs = connection.Query<CXWMS_KC>(sql).ToList();
                string sql1 = $"select COUNT(*) from  [dbo].[wms_kc] a\r\n" +
                    $"inner join [dbo].[wbd_kq] b on a.kq_code=b.kq_code\r\n" +
                    $"inner join [dbo].[wbd_kw] c on a.kw_code=c.kw_code\r\n" +
                    $"inner join [dbo].[bd_wl] d on a.wl_code=d.wl_code\r\n" +
                    $"inner join [dbo].[dictionary] e on e.dictionary_id=d.wl_type\r\n" +
                    $"inner join [dbo].[bd_dw] f on f.dw_id=d.wl_dw\r\n" +
                    $"where ck_code='{code}'";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYCXWMS_KC fYCXWMS_KC = new FYCXWMS_KC()
                {
                    cXWMS_KCs = cXWMS_KCs,
                    zon = zon,
                };
                return fYCXWMS_KC;
            }
        }
        public int UpdateWMS_KCXGTZ(int sl, string ckcode, string kqcode, string kwcode, string wlcode)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_kc] set kc_sl={sl},kc_krkl=kc_zdsl-{sl} where ck_code='{ckcode}' and kw_code='{kwcode}' and kq_code='{kqcode}' and wl_code='{wlcode}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public WMS_KC GetWMS_KCCKDL(string ck_code, string wl_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[wms_kc] where  ck_code='{ck_code}' and wl_code='{wl_code}'";
                WMS_KC rs = connection.Query<WMS_KC>(sql).FirstOrDefault();
                return rs;
            }
        }
        public List<WMS_KC> GetWMS_KCWL(string ck_code, string wl_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[wms_kc] where  ck_code='{ck_code}' and wl_code='{wl_code}'";
                List<WMS_KC> rs = connection.Query<WMS_KC>(sql).ToList();
                return rs;
            }
        }
    }
}
